﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using YookingBizWQW.Class.Public;
using YookingCrypto.Class;
using YookingDBNormal.Class;
using YookingKitBox.Class.Public;
using YookingSvrDBCall.Class.DBLogic;
using YookingSvrDBCall.Class.DBPub;

namespace YookingBizWQW.Class.Credit.pc
{
    public class clsAccountMgr
    {
        //注释：王庆文 2020年05月31日================================================================
        //查询已经审核通过的会员佣金提现计划信息、提现交易订单信息
        public string GetMemberCashoutInfo(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来
                                       
            string strUnitCode = "0000000"; //当前平台单位编码
            string strPlanStateCode = "99"; //转账状态
            string strMemberName = "Wtest"; //会员姓名
            string strMobilePhone = "0000"; //会员手机

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 4)
            {
                strUnitCode = strArray[0];
                strPlanStateCode = strArray[1];
                strMemberName = strArray[2];
                strMobilePhone = strArray[3];
            }

            #endregion

            string strSQLCondition = " 1=1 ";
            if (strPlanStateCode != "")
            {
                strSQLCondition += " AND T.PLANSTATE='" + strPlanStateCode + "' ";
            }

            if (strMemberName != "")
            {
                strSQLCondition += " AND B.PERSONNAME LIKE '%" + strMemberName + "%' ";
            }

            if (strMobilePhone != "")
            {
                strSQLCondition += " AND B.MOBILENUMBER='" + strMobilePhone + "' ";
            }

            #region 生成查询记录的SQL语句

            clsDBSpace mySpace = new clsDBSpace(); //表空间名
            string strDictSapace = mySpace.GetDBSpaceName("Dict");

            StringBuilder sbQuerySQL = new StringBuilder();
            sbQuerySQL.AppendLine(" SELECT ");
            sbQuerySQL.AppendLine(" 	T.UNITCODE, ");
            sbQuerySQL.AppendLine(" 	T.PERSONCODE, ");
            sbQuerySQL.AppendLine(" 	B.PERSONNAME, ");
            sbQuerySQL.AppendLine(" 	T.PLANID, ");
            sbQuerySQL.AppendLine(" 	T.PLANTIME, ");
            sbQuerySQL.AppendLine(" 	T.BANKCARDNO, ");
            sbQuerySQL.AppendLine(" 	T.PLANAMOUNT, ");
            sbQuerySQL.AppendLine(" 	T.VERIFYUNIT, ");
            sbQuerySQL.AppendLine(" 	T.VERIFYPERSON AS VERIFYPERSONCODE, ");
            sbQuerySQL.AppendLine(" 	C.PERSONNAME AS VERIFYPERSONNAME, ");
            sbQuerySQL.AppendLine(" 	T.VERIFYTIME, ");
            sbQuerySQL.AppendLine(" 	T.VERIFYRESULT AS VERIFYRESULTCODE, ");
            sbQuerySQL.AppendLine(" 	D.DICTMEMO AS VERIFYRESULTNAME, ");
            sbQuerySQL.AppendLine(" 	T.PLANSTATE AS PLANSTATECODE, ");
            sbQuerySQL.AppendLine(" 	E.DICTMEMO AS PLANSTATENAME, ");
            sbQuerySQL.AppendLine(" 	T.MEMBERLEVEL, ");
            sbQuerySQL.AppendLine(" 	A.PORDERNUM, ");
            sbQuerySQL.AppendLine(" 	A.REALSERVICEFEE, ");
            sbQuerySQL.AppendLine(" 	A.EXTRASERVICEFEE, ");
            sbQuerySQL.AppendLine(" 	A.ALLSERVICEFEE, ");
            sbQuerySQL.AppendLine(" 	A.MONEYSUM, ");
            sbQuerySQL.AppendLine(" 	A.SEBANKCARDNO, ");
            sbQuerySQL.AppendLine(" 	A.SEMOBILEPHONE, ");
            sbQuerySQL.AppendLine(" 	T.SYS_RECORDID AS PLAN_SYS_RECORDID, ");
            sbQuerySQL.AppendLine(" 	A.SYS_RECORDID AS TRADE_SYS_RECORDID, ");
            sbQuerySQL.AppendLine(" 	F.BANKCODE, ");
            sbQuerySQL.AppendLine(" 	G.DICTMEMO AS BANKNAME ");
            sbQuerySQL.AppendLine(" FROM XYK_CASHOUTPLAN T ");
            sbQuerySQL.AppendLine("   LEFT JOIN XYK_TRADEORDER A ON A.PLANID = T.PLANID ");
            sbQuerySQL.AppendLine("   LEFT JOIN SA_ORG_PERSONINFO B ON B.PERSONCODE = T.PERSONCODE ");
            sbQuerySQL.AppendLine("   LEFT JOIN SA_ORG_PERSONINFO C ON C.PERSONCODE = T.VERIFYPERSON ");
            sbQuerySQL.AppendLine("   LEFT JOIN " + strDictSapace + ".HOTEL_DICT_VERIFYRESULT D ON D.DICTCODE = T.VERIFYRESULT ");
            sbQuerySQL.AppendLine("   LEFT JOIN " + strDictSapace + ".DICT_XYK_TRADESTATE E ON E.DICTCODE = T.PLANSTATE ");
            sbQuerySQL.AppendLine("   LEFT JOIN XYK_BANKCARD F ON F.CARDNO=T.BANKCARDNO ");
            sbQuerySQL.AppendLine("   LEFT JOIN " + strDictSapace + ".DICT_XYK_BANKCODE G ON G.DICTCODE=F.BANKCODE ");
            sbQuerySQL.AppendLine(" WHERE " + clsSQLSyntax.IsNullSQL("Data", "T.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Data", "A.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Data", "B.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Data", "C.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Dict", "D.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Dict", "E.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Data", "F.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND " + clsSQLSyntax.IsNullSQL("Dict", "G.SYS_CANCELDATETIME"));
            sbQuerySQL.AppendLine("   AND T.VERIFYRESULT = '1' ");
            sbQuerySQL.AppendLine("   AND T.UNITCODE = '" + strUnitCode + "' ");
            sbQuerySQL.AppendLine("   AND " + strSQLCondition);

            #endregion

            //clsRWFile myLog = new clsRWFile(); //将待执行的SQL语句记录下来
            //myLog.WriteInfoToTxtFile("SysLog", "wqw.txt", sbQuerySQL.ToString());

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //实例化数据库操作类
            string strJsonArray = myDB.YookingExecuteJson("Data", sbQuerySQL.ToString());

            #region 将银行借记卡号码解密成明文字符串

            if (strJsonArray.Contains("yooking null"))
            {
                strRtData = strJsonArray; //直接返回空结果
            }
            else
            {
                Object objResult = JsonConvert.DeserializeObject(strJsonArray); //json数组文本转成对象
                JArray jAryResult = objResult as JArray; //把上面的objResult转换为Jobject对象

                //将密文银行借记卡号码转换成明文卡号           
                var strDes64Key = "W^=u2aPs"; //DES密钥
                var strDes64Iv = "2a9i@TaM";  //初始化向量
                clsSecret myDes = new clsSecret();
                for (int i = 0; i < jAryResult.Count; i++)
                {
                    string strEnBankCardNo = jAryResult[i]["BANKCARDNO"].ToString();
                    string strDeBankCardNo = myDes.DecryptString(strEnBankCardNo, strDes64Key, strDes64Iv);
                    jAryResult[i]["BANKCARDNO"] = strDeBankCardNo; //更新为明文银行卡号
                }

                //银行卡号解密后的新json数组
                strRtData = jAryResult.ToString();
            }

            #endregion
           
            return strRtData; //数据库查询结果
        }

        //注释：王庆文 2020年05月31日=====================================================================
        //更新表XYK_CashoutPlan、XYK_TradeOrder的交易状态、
        //更新表XYK_Income_Bill，将对应金额的佣金记录状态改为“已提现”
        public string FinishCashoutPlan(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strPlan_Sys_RecordID = "00000"; //表XYK_CashoutPlan的记录ID
            string strTrade_Sys_RecordID = "0000"; //表XYK_TradeOrder的记录ID
            string strUnitCode = "00000000000000"; //当前平台单位编码
            string strPersonCode = "000000000000"; //会员人员编码
            string strPlanAmount = "0";            //计划提现金额

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 5)
            {
                strPlan_Sys_RecordID = strArray[0];
                strTrade_Sys_RecordID = strArray[1];
                strUnitCode = strArray[2];
                strPersonCode = strArray[3];
                strPlanAmount = strArray[4];
            }

            #endregion

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //实例化数据库操作类

            #region 更新表XYK_CashoutPlan的交易状态

            StringBuilder sbUpdateSQL01 = new StringBuilder();
            sbUpdateSQL01.AppendLine(" update XYK_CashoutPlan set PlanState='00' ");
            sbUpdateSQL01.AppendLine(" where Sys_RecordID='" + strPlan_Sys_RecordID + "' ");

            //更新提现计划数据库表的对应记录
            object objPlan = myDB.YookingExecuteNonQuery("Data", sbUpdateSQL01.ToString());

            strRtData = "【已更新提现计划行数："+ objPlan.ToString() + "】";

            #endregion

            #region 更新表XYK_TradeOrder的交易状态

            string strNowTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            StringBuilder sbUpdateSQL02 = new StringBuilder();
            sbUpdateSQL02.AppendLine(" update XYK_TradeOrder set ");
            sbUpdateSQL02.AppendLine("   InterCallTime='" + strNowTime + "', ");
            sbUpdateSQL02.AppendLine("   RealTradeTime='" + strNowTime + "', ");
            sbUpdateSQL02.AppendLine("   RealTradeState='00', ");
            sbUpdateSQL02.AppendLine("   RealSettTime='" + strNowTime + "', ");
            sbUpdateSQL02.AppendLine("   RealSettState='00' ");
            sbUpdateSQL02.AppendLine(" where Sys_RecordID='" + strTrade_Sys_RecordID + "' ");

            //更新订单交易情况登记表的对应记录
            object objTrade = myDB.YookingExecuteNonQuery("Data", sbUpdateSQL02.ToString());

            strRtData += "【已更新提现交易订单行数：" + objTrade.ToString() + "】";

            #endregion

            #region 更新表XYK_Income_Bill，将对应金额的佣金记录状态改为“已提现”

            int iAtmBillCount = 0; //已经更新了提现状态的记录总行数
            double dUpdateAmount = 0; //被更新了提现状态的记录的累计金额
            double dPlanAmount = Convert.ToDouble(strPlanAmount);

            do
            {
                #region 先找出时间最早的一条未提现的佣金记录

                StringBuilder sbQuerySQL = new StringBuilder();
                sbQuerySQL.AppendLine(" SELECT  ");            
                sbQuerySQL.AppendLine("   t.UnitCode, ");
                sbQuerySQL.AppendLine("   t.ReceCode, ");
                sbQuerySQL.AppendLine("   t.GiveCode, ");
                sbQuerySQL.AppendLine("   t.BkgeType, ");
                sbQuerySQL.AppendLine("   t.BkgeFrom, ");
                sbQuerySQL.AppendLine("   t.BkgeTime, ");
                sbQuerySQL.AppendLine("   t.BkgeAmount, ");
                sbQuerySQL.AppendLine("   t.IsSuccess, ");
                sbQuerySQL.AppendLine("   t.ATMState, ");
                sbQuerySQL.AppendLine("   t.ATMTime, ");
                sbQuerySQL.AppendLine("   t.DBTblName, ");
                sbQuerySQL.AppendLine("   t.OrderID, ");
                sbQuerySQL.AppendLine("   t.TradeAmount, ");
                sbQuerySQL.AppendLine("   t.ServiceFee, ");
                sbQuerySQL.AppendLine("   t.BkgeRate, ");
                sbQuerySQL.AppendLine("   t.OtherMemo, ");
                sbQuerySQL.AppendLine("   t.Sys_RecordID ");
                sbQuerySQL.AppendLine(" FROM xyk_income_bill t ");
                sbQuerySQL.AppendLine(" WHERE " + clsSQLSyntax.IsNullSQL("Data", "t.SYS_CANCELDATETIME"));
                sbQuerySQL.AppendLine(" AND t.IsSuccess = '1' ");
                sbQuerySQL.AppendLine(" AND t.ATMState = '0' ");
                sbQuerySQL.AppendLine(" AND t.UnitCode = '" + strUnitCode + "' ");
                sbQuerySQL.AppendLine(" AND t.ReceCode = '" + strPersonCode + "' ");
                sbQuerySQL.AppendLine(" AND t.BkgeTime = ( ");
                sbQuerySQL.AppendLine(" 	SELECT min(BkgeTime) ");
                sbQuerySQL.AppendLine("     FROM xyk_income_bill ");
                sbQuerySQL.AppendLine(" 	WHERE " + clsSQLSyntax.IsNullSQL("Data", "SYS_CANCELDATETIME"));
                sbQuerySQL.AppendLine(" 	AND IsSuccess = '1' ");
                sbQuerySQL.AppendLine(" 	AND ATMState = '0' ");
                sbQuerySQL.AppendLine(" 	AND UnitCode = '" + strUnitCode + "' ");
                sbQuerySQL.AppendLine(" 	AND ReceCode = '" + strPersonCode + "' ");
                sbQuerySQL.AppendLine(" ) ");

                #endregion

                DataSet dsBkge = myDB.YookingExecuteDataSet("Data", sbQuerySQL.ToString());
                if (dsBkge != null && dsBkge.Tables.Count > 0 && dsBkge.Tables[0].Rows.Count > 0)
                {
                    //该行记录的佣金金额、以及记录ID
                    string strBkgeAmount = dsBkge.Tables[0].Rows[0]["BkgeAmount"].ToString();
                    string strBill_Sys_RecordID = dsBkge.Tables[0].Rows[0]["Sys_RecordID"].ToString();

                    if (strBkgeAmount == "") strBkgeAmount = "0"; //空值处理
                    double dBkgeAmount = Convert.ToDouble(strBkgeAmount);

                    //累计修改了提现状态的总额
                    dUpdateAmount = dUpdateAmount + dBkgeAmount; 

                    if (dUpdateAmount <= dPlanAmount)
                    {
                        #region 直接修改提现状态

                        string strAtmTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                        StringBuilder sbUpdateSQL03 = new StringBuilder();
                        sbUpdateSQL03.AppendLine(" update XYK_Income_Bill set ");
                        sbUpdateSQL03.AppendLine("   ATMState='1', ");
                        sbUpdateSQL03.AppendLine("   ATMTime='" + strAtmTime + "' ");
                        sbUpdateSQL03.AppendLine(" where Sys_RecordID='" + strBill_Sys_RecordID + "' ");

                        //将佣金提现状态改为：“已提现”、并记录提现时间
                        object objBill = myDB.YookingExecuteNonQuery("Data", sbUpdateSQL03.ToString());
                        iAtmBillCount = iAtmBillCount + Convert.ToInt32(objBill); //已经提现的总行数

                        #endregion
                    }
                    else
                    {
                        double dDiffValue = dUpdateAmount - dPlanAmount; //多出来的金额
                        double dReduceAmount = dBkgeAmount - dDiffValue; //实际要扣掉的金额

                        dDiffValue = Math.Round(dDiffValue, 2);       //保留小数点后两位
                        dReduceAmount = Math.Round(dReduceAmount, 2); //保留小数点后两位

                        //佣金提现，在修改提现状态时，可能会出现被重置提现状态的
                        //全部记录加起来的总额大于实际计划金额的情况，这种情况下，需要对多
                        //出来的金额进行适当的拆分，一部分改为“已提现”，剩下的部分状态仍为“未提现”。
                        string strRtResult = SplitIncomeBill(dsBkge, dReduceAmount);
                        if (strRtResult != "no data") //佣金拆分成功，则修改并减少原佣金金额
                        {
                            #region 修改当前记录的佣金额度 = dDiffValue

                            StringBuilder sbUpdateSQL04 = new StringBuilder();
                            sbUpdateSQL04.AppendLine(" update XYK_Income_Bill set ");
                            sbUpdateSQL04.AppendLine("   BkgeAmount='" + dDiffValue.ToString() + "', ");
                            sbUpdateSQL04.AppendLine("   OtherMemo='由于提现凑整导致的佣金拆分。原订单金额减少，新订单直接改为已提现。' ");
                            sbUpdateSQL04.AppendLine(" where Sys_RecordID='" + strBill_Sys_RecordID + "' ");

                            //修改佣金金额为减去已经提现部分额度
                            object objDiff = myDB.YookingExecuteNonQuery("Data", sbUpdateSQL04.ToString());
                            iAtmBillCount = iAtmBillCount + Convert.ToInt32(objDiff); //已经提现的总行数

                            #endregion
                        }
                    }
                }
            }
            while (dUpdateAmount < dPlanAmount);

            strRtData += "【已更新佣金提现记录行数：" + iAtmBillCount.ToString() + "】";

            #endregion

            return strRtData; //佣金提现状态处理结果
        }

        //注释：王庆文 2020年05月31日=================================================================================
        //佣金提现，在修改提现状态时，可能会出现被重置提现状态的全部记录加起来的总额大于实际计划金额的情况，
        //这种情况下，需要对多出来的金额进行适当的拆分，一部分改为“已提现”，剩下的部分状态仍为“未提现”。
        private string SplitIncomeBill(DataSet dsBkge, double dReduceAmount)
        {
            string strRtResult = "no data";

            if (dsBkge != null && dsBkge.Tables.Count > 0 && dsBkge.Tables[0].Rows.Count > 0)
            {
                DataTable dtBkge = dsBkge.Tables[0]; //实例化DataTable

                #region 从DataTable中解析出各个字段的对应值

                string strUnitCode = dtBkge.Rows[0]["UnitCode"].ToString();         //平台编码
                string strReceCode = dtBkge.Rows[0]["ReceCode"].ToString();         //受益人编码
                string strGiveCode = dtBkge.Rows[0]["GiveCode"].ToString();         //贡献人编码
                string strBkgeType = dtBkge.Rows[0]["BkgeType"].ToString();         //佣金类型
                string strBkgeFrom = dtBkge.Rows[0]["BkgeFrom"].ToString();         //佣金来源,POS01=信用卡刷卡、POS02=信用卡还款、ATM01=信用卡套刷、ATM02=信用卡套入、HBEI01=花呗消费、HBEI02=花呗还款、BKGE01=佣金提现、HYCZ01=会员充值
                string strBkgeTime = dtBkge.Rows[0]["BkgeTime"].ToString();         //计佣时间
                string strBkgeAmount = dtBkge.Rows[0]["BkgeAmount"].ToString();     //佣金金额
                string strIsSuccess = dtBkge.Rows[0]["IsSuccess"].ToString();       //是否分润成功,0=分润失败，1=分润成功
                string strATMState = dtBkge.Rows[0]["ATMState"].ToString();         //支取状态,0=未支取，1=已支取
                string strATMTime = dtBkge.Rows[0]["ATMTime"].ToString();           //支取时间
                string strDBTblName = dtBkge.Rows[0]["DBTblName"].ToString();       //资金发生库
                string strOrderID = dtBkge.Rows[0]["OrderID"].ToString();           //交易订单号
                string strTradeAmount = dtBkge.Rows[0]["TradeAmount"].ToString();   //交易总金额
                string strServiceFee = dtBkge.Rows[0]["ServiceFee"].ToString();     //交易服务费
                string strBkgeRate = dtBkge.Rows[0]["BkgeRate"].ToString();         //返佣比例
                string strOtherMemo = dtBkge.Rows[0]["OtherMemo"].ToString();       //其他备注
                string strSys_RecordID = dtBkge.Rows[0]["Sys_RecordID"].ToString(); //记录ID

                #endregion

                #region 生成即将写入数据库的json数组

                string strNowTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                JArray objJArray = new JArray();
                JObject objJObject = new JObject();
                objJObject.Add("UnitCode", strUnitCode);                //平台编码
                objJObject.Add("ReceCode", strReceCode);                //受益人编码
                objJObject.Add("GiveCode", strGiveCode);                //贡献人编码
                objJObject.Add("BkgeType", strBkgeType);                //佣金类型
                objJObject.Add("BkgeFrom", strBkgeFrom);                //佣金来源
                objJObject.Add("BkgeTime", strBkgeTime);                //计佣时间
                objJObject.Add("BkgeAmount", dReduceAmount.ToString()); //佣金金额
                objJObject.Add("IsSuccess", strIsSuccess);              //是否分润成功
                objJObject.Add("ATMState", "1");                        //支取状态
                objJObject.Add("ATMTime", strNowTime);                  //支取时间
                objJObject.Add("DBTblName", strDBTblName);              //资金发生库
                objJObject.Add("OrderID", strOrderID);                  //交易订单号
                objJObject.Add("TradeAmount", strTradeAmount);          //交易总金额
                objJObject.Add("ServiceFee", strServiceFee);            //交易服务费
                objJObject.Add("BkgeRate", strBkgeRate);                //返佣比例
                objJObject.Add("OtherMemo", "由于提现凑整导致的佣金拆分。原订单金额减少，新订单直接改为已提现。"); //其他备注

                objJArray.Add(objJObject); 

                #endregion

                //clsRWFile myLog = new clsRWFile(); //将请求结果保存到txt文件中
                //myLog.WriteInfoToTxtFile("SysLog", "wqw.txt", objJArray.ToString(), false);

                DataSet dsData = clsJConvert.JArryToDataSet(objJArray.ToString()); //将Json数组转成DataSet

                clsWriteDB myWrite = new clsWriteDB(); //先判断数据是否存在，根据判断结果自动执行insert或者update
                string strKeyColSet = "UnitCode,ReceCode,GiveCode,BkgeType,BkgeAmount,OrderID,ATMTime,DBTblName"; //重值判断
                strRtResult = myWrite.WriteDBNormal_UseSYSCol("Data", "XYK_Income_Bill", strKeyColSet, "02120119901", dsData);
            }

            return strRtResult; //返回数据insert结果
        }




    }
}
